#include "BusinessLogic.h"
#include "../CommonUtil/messages.h"

/************************* Business Logic Functions ***********************************/

bool CBusinessLogic::UserRegister(const char* pAccount, const char* pPasswd)
{
	if (pAccount == NULL || pPasswd == NULL)
		return false;
	if (strlen(pAccount) == 0 || strlen(pPasswd) == 0)
		return false;

	char szSql[1024];
	sprintf(szSql, "insert into account values ('%s','%s')", pAccount, pPasswd);

	return ExecuteSQL(szSql);
}

bool CBusinessLogic::UserAuth(const char* pAccount, const char* pPasswd)
{
	if (pAccount == NULL || pPasswd == NULL)
		return false;

	char szSql[1024];
	sprintf(szSql, "select * from account where account = '%s'", pAccount);

	if (!ExecuteSQL(szSql) || colData.size() <= 0)
		return false;

	return strcmp(colData[0][1].c_str(), pPasswd) == 0;
}

bool CBusinessLogic::AirLineRegister(const char* pAccount, const char* pPasswd)
{
	if (pAccount == NULL || pPasswd == NULL)
		return false;
	if (strlen(pAccount) == 0 || strlen(pPasswd) == 0)
		return false;

	char szSql[1024];
	sprintf(szSql, "insert into Airline values ('%s','%s')", pAccount, pPasswd);

	return ExecuteSQL(szSql);
}

bool CBusinessLogic::AirLineAuth(const char* pAccount, const char* pPasswd)
{
	if (pAccount == NULL || pPasswd == NULL)
		return false;

	char szSql[1024];
	sprintf(szSql, "select * from Airline where airline = '%s'", pAccount);

	if (!ExecuteSQL(szSql) || colData.size() <= 0)
		return false;

	return strcmp(colData[0][1].c_str(), pPasswd) == 0;
}

void CBusinessLogic::UserSelRoute(IN MSG_ROUTE_SEL& sel, OUT vector<ROUT_ENTRY>& vecRes)
{
	const char* pCabin = NULL;
	if (sel.nCabinType == CABINE_BUSINESS)
		pCabin = "business";
	else if (sel.nCabinType == CABINE_FIRSTCLASS)
		pCabin = "first";
	else 
		pCabin = "economy";

	char szSql[1024];
	sprintf(szSql, "select * from Route where start_addr = '%s' and \
		destination = '%s' and departure_time >= '%s' and departure_time <= '%s' and %s > 0", 
		sel.strStartAddr, sel.strDestination, sel.depTimeBeg, sel.depTimeEnd, pCabin);

	if (!ExecuteSQL(szSql))
		return;

	for (vector<vector<string>>::iterator it = colData.begin();
		it != colData.end(); it++)
	{
		vector<string>& dbEntry = *it;
		ROUT_ENTRY entry;

		string strId;
		GetFieldByColName(dbEntry, "id", strId);
		entry.id = atoi(strId.c_str());

		//strFlightID
		string strFlightID;
		GetFieldByColName(dbEntry, "flightID", strFlightID);
		strcpy(entry.strFlightID, strFlightID.c_str());

		//strAirLine
		string strAirLine;
		GetFieldByColName(dbEntry, "airline", strAirLine);
		strcpy(entry.strAirLine, strAirLine.c_str());

		//start_addr
		string strStartAddr;
		GetFieldByColName(dbEntry, "start_addr", strStartAddr);
		strcpy(entry.strStartAddr, strStartAddr.c_str());

		//destination
		string strDestination;
		GetFieldByColName(dbEntry, "destination", strDestination);
		strcpy(entry.strDestination, strDestination.c_str());

		//destination
		string strDepTime;
		GetFieldByColName(dbEntry, "departure_time", strDepTime);
		strcpy(entry.depTime, strDepTime.c_str());

		//arrive_time
		string strArvTime;
		GetFieldByColName(dbEntry, "arrive_time", strArvTime);
		strcpy(entry.arvTime, strArvTime.c_str());

		//business
		string strBusiness;
		GetFieldByColName(dbEntry, "business", strBusiness);
		entry.nBusiness = atoi(strBusiness.c_str());

		//first
		string strFirst;
		GetFieldByColName(dbEntry, "first", strFirst);
		entry.nFirstClass = atoi(strFirst.c_str());

		//economy
		string strEconomy;
		GetFieldByColName(dbEntry, "economy", strEconomy);
		entry.nEconomy = atoi(strEconomy.c_str());

		vecRes.push_back(entry);
	}
}

bool CBusinessLogic::UserOrderRoute(const char* szAccount, int nRouteId, CABIN_TYPE nCabTp)
{
	if (szAccount == NULL)
		return false;

	//verify user existence
	char szSql[1024];
	sprintf(szSql, "select * from account where account = '%s'", szAccount);
	if (!ExecuteSQL(szSql) || colData.size() <= 0)
		return false;

	//get correspondent route
	sprintf(szSql, "select * from Route where id = %d", nRouteId);
	if (!ExecuteSQL(szSql) || colData.size() <= 0)
		return false;

	//check cabin is available
	string strRes;
	const char* pCabin = NULL;
	if (nCabTp == CABINE_BUSINESS)
		pCabin = "business";
	else if (nCabTp == CABINE_ECONOMY)
		pCabin = "economy";
	else if (nCabTp == CABINE_FIRSTCLASS)
		pCabin = "first";

	if (NULL == pCabin)
		return false;

	GetFieldByColName(colData[0], pCabin, strRes);
	int nNum = atoi(strRes.c_str());
	if (nNum <= 0) 
		return false;

	sprintf(szSql, "update Route set %s = %d where id = %d", pCabin, nNum-1, nRouteId);
	if (!ExecuteSQL(szSql))
		return false;

	//insert order record
	sprintf(szSql, "insert into Reservation values ('%s', %d, %d)", 
		szAccount, nRouteId, nCabTp);
	if (!ExecuteSQL(szSql))
		return false;

	return true;
}

void CBusinessLogic::ViewOrders(bool bCus, const char* szAccount, vector<ORDER_ENTRY>& vecRes)
{
	char szSql[1024];

	const char* pCond = "Reservation.customer_account";
	if (!bCus)
		pCond = "Route.airline";

	sprintf(szSql, "select Reservation.id, \
		Route.flightID, Route.airline, Route.start_addr, Route.destination, \
		Route.departure_time, Route.arrive_time, Reservation.cabin from \
		Reservation, Route where %s = '%s' \
		AND Reservation.routeID = Route.id", pCond, szAccount);

	if (!ExecuteSQL(szSql))
		return;

	for (vector<vector<string>>::iterator it = colData.begin();
		it != colData.end(); it++)
	{
		ORDER_ENTRY entry;
		string strRes;
		GetFieldByColName(*it, "id", strRes);
		entry.id = atoi(strRes.c_str());

		GetFieldByColName(*it, "flightID", strRes);
		strcpy(entry.strFlightID, strRes.c_str());

		GetFieldByColName(*it, "airline", strRes);
		strcpy(entry.strAirLine, strRes.c_str());

		GetFieldByColName(*it, "start_addr", strRes);
		strcpy(entry.strStartAddr, strRes.c_str());

		GetFieldByColName(*it, "destination", strRes);
		strcpy(entry.strDestination, strRes.c_str());

		GetFieldByColName(*it, "departure_time", strRes);
		strcpy(entry.depTime, strRes.c_str());

		GetFieldByColName(*it, "arrive_time", strRes);
		strcpy(entry.arvTime, strRes.c_str());

		GetFieldByColName(*it, "cabin", strRes);
		entry.nCabinType = (CABIN_TYPE)atoi(strRes.c_str());

		vecRes.push_back(entry);
	}
}

bool CBusinessLogic::CancelAnOrder(bool bCus, const char* szName, int nOrderId)
{
	char szSql[1024];
	sprintf(szSql, "select * from Reservation where id = %d", nOrderId);
	if (szName == NULL || !ExecuteSQL(szSql) || colData.size() <= 0)
		return false;

	string strRes;
	GetFieldByColName(colData[0], "cabin", strRes);
	int nNum = atoi(strRes.c_str());

	GetFieldByColName(colData[0], "routeID", strRes);
	int nRouteId = atoi(strRes.c_str());

	if (bCus)
	{
		GetFieldByColName(colData[0], "customer_account", strRes);
		if (strcmp(strRes.c_str(), szName) != 0)
			return false;
	}
	else
	{
		sprintf(szSql, "select * from Route where id = %d", nRouteId);

		if (!ExecuteSQL(szSql))	
			return false;

		GetFieldByColName(colData[0], "airline", strRes);
		if (strcmp(strRes.c_str(), szName) != 0)
			return false;
	}

	const char* pCabin = NULL;
	if (nNum == CABINE_BUSINESS)
		pCabin = "business";
	else if (nNum == CABINE_ECONOMY)
		pCabin = "economy";
	else if (nNum == CABINE_FIRSTCLASS)
		pCabin = "first";
	else 
		return false;

	if (NULL == pCabin)
		return false;

	sprintf(szSql, "select * from Route where id = %d", nRouteId);
	if (!ExecuteSQL(szSql))	
		return false;

	GetFieldByColName(colData[0], pCabin, strRes);
	nNum = atoi(strRes.c_str());

	sprintf(szSql, "delete from Reservation where id = %d", nOrderId);
	if (!ExecuteSQL(szSql))
		return false;

	sprintf(szSql, "update Route set %s = %d where id = %d", pCabin, nNum+1, nRouteId);
	if (!ExecuteSQL(szSql))
		return false;

	return true;
}

bool CBusinessLogic::AddRoute(ROUT_ENTRY& route)
{
	char szSql[1024];
	sprintf(szSql, "insert into Route values ('%s', '%s', '%s', '%s', '%s', '%s', %d, %d, %d)", 
		route.strFlightID, route.strAirLine, route.strStartAddr, route.strDestination, 
		route.depTime, route.arvTime, route.nBusiness, route.nFirstClass, route.nEconomy);
	if (!ExecuteSQL(szSql))
		return false;

	return true;
}

void CBusinessLogic::ViewRoutes(const char* szAirline, OUT vector<ROUT_ENTRY>& vecRes)
{
	if (szAirline == NULL) return;

	char szSql[1024];
	sprintf(szSql, "select * from Route where airline = '%s'", szAirline);
	if (!ExecuteSQL(szSql))
		return;

	for (vector<vector<string>>::iterator it = colData.begin();
		it != colData.end(); it++)
	{
		vector<string>& dbEntry = *it;
		ROUT_ENTRY entry;

		string strId;
		GetFieldByColName(dbEntry, "id", strId);
		entry.id = atoi(strId.c_str());

		//strFlightID
		string strFlightID;
		GetFieldByColName(dbEntry, "flightID", strFlightID);
		strcpy(entry.strFlightID, strFlightID.c_str());

		//strAirLine
		string strAirLine;
		GetFieldByColName(dbEntry, "airline", strAirLine);
		strcpy(entry.strAirLine, strAirLine.c_str());

		//start_addr
		string strStartAddr;
		GetFieldByColName(dbEntry, "start_addr", strStartAddr);
		strcpy(entry.strStartAddr, strStartAddr.c_str());

		//destination
		string strDestination;
		GetFieldByColName(dbEntry, "destination", strDestination);
		strcpy(entry.strDestination, strDestination.c_str());

		//destination
		string strDepTime;
		GetFieldByColName(dbEntry, "departure_time", strDepTime);
		strcpy(entry.depTime, strDepTime.c_str());

		//arrive_time
		string strArvTime;
		GetFieldByColName(dbEntry, "arrive_time", strArvTime);
		strcpy(entry.arvTime, strArvTime.c_str());

		//business
		string strBusiness;
		GetFieldByColName(dbEntry, "business", strBusiness);
		entry.nBusiness = atoi(strBusiness.c_str());

		//first
		string strFirst;
		GetFieldByColName(dbEntry, "first", strFirst);
		entry.nFirstClass = atoi(strFirst.c_str());

		//economy
		string strEconomy;
		GetFieldByColName(dbEntry, "economy", strEconomy);
		entry.nEconomy = atoi(strEconomy.c_str());

		vecRes.push_back(entry);
	}
}

/***************************************************************************************/

CBusinessLogic::CBusinessLogic(void)
{
	SQLRETURN rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_envHandle);
	if (rc == SQL_SUCCESS)
		rc = SQLSetEnvAttr(m_envHandle, SQL_ATTR_ODBC_VERSION,
		(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
	
	if (rc == SQL_SUCCESS)
		rc = SQLAllocHandle(SQL_HANDLE_DBC, m_envHandle, &m_conHandle);
}

CBusinessLogic::~CBusinessLogic(void)
{
	if (m_conHandle != NULL) 
		SQLDisconnect(m_conHandle);
	if (m_conHandle != NULL)
		SQLFreeHandle(SQL_HANDLE_DBC, m_conHandle);
	if (m_envHandle != NULL)
		SQLFreeHandle(SQL_HANDLE_ENV, m_envHandle);
}

bool CBusinessLogic::GetFieldByColName(IN vector<string>& entry, 
	IN const char* szColName, OUT string& strRes)
{
	int i = 0;
	for (; i < cols.size(); i++)
	{
		if (strcmp((const char*)(cols.at(i)).colName, szColName) == 0)
			break;
	}

	if (i >= cols.size())
		return false;

	strRes = entry.at(i);
	return true;
}

SQLRETURN CBusinessLogic::GetColData(int colnum, string& str)
{
	SQLRETURN rc;
	SQLCHAR buf[255] = {0};
	if( (rc = SQLGetData(m_stmtHandle, colnum, SQL_CHAR, buf, sizeof(buf), NULL)) == SQL_SUCCESS)
		str = reinterpret_cast<char*>(buf);
	return rc;
}

SQLRETURN CBusinessLogic::GetResultset()
{
	DescribeColumns();
	colData.clear();
	
	while( SQLFetch(m_stmtHandle) == SQL_SUCCESS)
	{
		vector<string> col;
		string data;
		int i = 1;
		while( GetColData(i, data) == SQL_SUCCESS)
		{
			col.push_back(data);
			++i; 
		}
		
		colData.push_back(col);
	}
	return SQL_SUCCESS;
}

SQLRETURN  CBusinessLogic::Describe(ColDescription& c)
{
	return SQLDescribeCol(m_stmtHandle,c.colNumber,
		c.colName, sizeof(c.colName), &c.nameLen,
		&c.dataType, &c.colSize, &c.decimalDigits, &c.nullable);
}

void CBusinessLogic::DescribeColumns()
{
	ColDescription c;
	c.colNumber = 1;
	cols.clear();
	while( Describe(c) == SQL_SUCCESS)
	{
		cols.push_back(c);
		++c.colNumber;
	}

}

bool CBusinessLogic::ConnDataBase(const char* szDSN)
{
	if (m_conHandle != NULL)
	{
		SQLRETURN rc = SQLConnect(m_conHandle, (SQLCHAR*)szDSN, SQL_NTS,
			NULL, 0, NULL, 0);
		if (!((rc==SQL_SUCCESS)||(rc==SQL_SUCCESS_WITH_INFO)))
			return false;
		return true;
	}

	return false;
}

bool CBusinessLogic::ExecuteSQL(const char* szSql)
{
	if (NULL == szSql)
		return false;

	SQLRETURN rc = SQLAllocHandle(SQL_HANDLE_STMT, m_conHandle, &m_stmtHandle);
	if (rc != SQL_SUCCESS)
		return false;
	
	SQLCHAR SQLStmt[1024] = {0};
	strcpy((char *) SQLStmt, szSql);
		
	rc = SQLExecDirect(m_stmtHandle, SQLStmt, SQL_NTS);
	if (rc != SQL_SUCCESS)
		return false;
	
	GetResultset();
	
	if (m_stmtHandle != NULL)
		SQLFreeHandle(SQL_HANDLE_STMT, m_stmtHandle);

	return true;
}

CBusinessLogic& CBusinessLogic::GetInstance()
{
	static CBusinessLogic instance;
	return instance;
}